/*
 * Copyright (c) 2019.  鼎捷软件股份有限公司.All rights reserved.
 */

package com.digiwin.dap.middleware.util;

/**
 * @author :  chenzwd
 * @date :    2019/1/18
 */

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.persistence.Column;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class JdbcUtils {

    private static Pattern regex = Pattern.compile("[A-Z]+");


    private String username;
    private String password;
    private final String driver = "com.mysql.jdbc.Driver";
    private String url;
    Connection connection;
    private Logger logger = LoggerFactory.getLogger(JdbcUtils.class);

    /**
     * 创建数据工具
     */
    public static JdbcUtils create(String url, String user, String password) {
        return new JdbcUtils(url, user, password);
    }

    private JdbcUtils(String url, String user, String password) {
        this.url = url;
        this.username = user;
        this.password = password;
        try {
            Class.forName(driver);
            System.out.println("注册驱动成功！！");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            logger.error(e.getMessage(), e);
            System.out.println("注册驱动失败！！");
        }
    }

    private Connection getConnection() {
        try {
            if(connection == null) {
                connection = DriverManager.getConnection(url, username, password);
            }
            return connection;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            System.out.println("Connection com.digiwin.middleware.core.exception !");
        }
        return null;
    }

    /**
     * 完成对数据库标的增加删除和修改的操作
     */
    public boolean updateByPreparedStatement(String sql, List<Object> params)
            throws SQLException {
        // 定义sql语句的执行对象
        PreparedStatement pstmt = null;
        // 定义查询返回的结果集合
        ResultSet resultSet = null;
        try {
            boolean flag;
            // 表示当用户执行增加删除和修改的操作影响的行数
            int result = -1;
            // 表示 占位符 ，从1开始
            int index = 1;
            pstmt = this.getConnection().prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    // 填充占位符
                    pstmt.setObject(index++, params.get(i));
                }
            }

            result = pstmt.executeUpdate();
            flag = result > 0 ? true : false;
            return flag;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
        }
    }


    /**
     * 查询返回单个JavaBean(使用java反射机制)
     * @throws Exception
     */
    public <T> T findSimpleRefResult(String sql, List<Object> params,
                                     Class<T> cls) throws Exception {
        // 定义sql语句的执行对象
        PreparedStatement pstmt = null;
        // 定义查询返回的结果集合
        ResultSet resultSet = null;
        try {
            int index = 1;
            // 填充占位符
            pstmt = this.getConnection().prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            // 获取查询结果
            resultSet = pstmt.executeQuery();


            // 获取总的列数
            List<Field> fieldList = ReflectionUtils.getDeclaredFields(cls);
            T resultObject = cls.newInstance();
            while (resultSet.next()) {
                fillFieldValue(cls, fieldList, resultSet, resultObject);
                break;
            }
            return resultObject;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
        }

    }

    private <T> void fillFieldValue(Class<T> cls, List<Field> fieldList, ResultSet resultSet, T resultObject) throws SQLException, IllegalAccessException {
        // 获取列的信息

        ResultSetMetaData metaData = resultSet.getMetaData();
        for (Field field : fieldList) {
            Column column = field.getAnnotation(Column.class);
            String columnName;
            if (column == null || StringUtils.isEmpty(column.name())) {
                columnName = field.getName();
                Matcher matcher = JdbcUtils.regex.matcher(columnName);
                if (matcher.find()) {
                    columnName = matcher.replaceAll("_$0");
                }
                columnName = columnName.toUpperCase();
            } else {
                columnName = column.name().toUpperCase();
                if (columnName.startsWith("[")) {
                    columnName = columnName.replace("[", "");
                    columnName = columnName.replace("]", "");
                }
            }
            for (int colIndex = 1; colIndex <= metaData.getColumnCount(); colIndex++) {
                if (metaData.getColumnName(colIndex).toUpperCase().equals(columnName) ||
                        metaData.getColumnLabel(colIndex).toUpperCase().equals(columnName)) {
                    // 获取第i列的值
                    Object colValue = resultSet.getObject(metaData.getColumnLabel(colIndex));
                    if (colValue != null) {
                        // 打开 JavaBean的访问 private权限
                        field.setAccessible(true);
                        String datetimeType = "DATETIME";
                        if (metaData.getColumnTypeName(colIndex).equals(datetimeType)) {
                            DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S");
                            colValue = LocalDateTime.parse(colValue.toString(), df);
                        }
                        field.set(resultObject, colValue);
                    }
                    break;
                }
            }
        }
    }


    /**
     * 查询返回多个JavaBean(通过java反射机制)
     *
     * @throws Exception
     */
    public <T> List<T> findMoreRefResult(String sql, List<Object> params,
                                         Class<T> cls) throws Exception {
        // 定义sql语句的执行对象
        PreparedStatement pstmt = null;
        // 定义查询返回的结果集合
        ResultSet resultSet = null;
        try {
            List<T> list = new ArrayList<T>();
            //占位符
            int index = 1;
            pstmt = this.getConnection().prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            // 返回查询结果集合
            resultSet = pstmt.executeQuery();
            // 返回列的信息
            List<Field> fieldList = ReflectionUtils.getDeclaredFields(cls);
            while (resultSet.next()) {
                // 通过反射机制创建一个java实例
                T resultObject = cls.newInstance();
                fillFieldValue(cls, fieldList, resultSet, resultObject);
                list.add(resultObject);

            }
            return list;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
        }
    }

    public void closeConnection() throws SQLException {
        connection.close();
    }


}